/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.edu.fpt.xml.hrm.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;
import vn.edu.fpt.xml.hrm.common.AppConstant;
import vn.edu.fpt.xml.hrm.common.FormatUtil;
import vn.edu.fpt.xml.hrm.pojo.Payroll;

/**
 *
 * @author HoangTN
 */
public class PayrollBean {

    private String dbms;
    private String serverName;
    private String port;
    private String dbName;
    private boolean integratedSecurity;
    private String username;
    private String password;

    public PayrollBean() {
        dbms = AppConstant.DB_DBMS;
        serverName = AppConstant.DB_SERVER_NAME;
        port = AppConstant.DB_PORT;
        dbName = AppConstant.DB_NAME;
        integratedSecurity = AppConstant.DB_INTEGRATED_SECURITY;
        username = AppConstant.DB_USERNAME;
        password = AppConstant.DB_PASSWORD;
    }

    private Connection connect() throws ClassNotFoundException, SQLException, NamingException {
        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();
        return con;
    }

    public Connection connectDB() throws ClassNotFoundException, SQLException {

        String url = "jdbc:" + dbms + "://" + serverName + ":" + port;
        Properties properties = new Properties();
        properties.put("databaseName", dbName);
        if (integratedSecurity) {
            properties.put("integratedSecurity", true);
        } else {
            properties.put("username", username);
            properties.put("password", password);
        }
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection connection = DriverManager.getConnection(url, properties);
        return connection;
    }

    public List<Payroll> getAll() throws SQLException {
        Connection connection = null;
        List<Payroll> list = new ArrayList<Payroll>();
        try {
            connection = connect();
            String sql = "SELECT * FROM Payroll";
            PreparedStatement stm = connection.prepareStatement(sql);
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                Payroll payroll = new Payroll();
                payroll.setPayrollID(BigInteger.valueOf(rs.getInt("PayrollID")));
                payroll.setEmployeeName(rs.getString("EmployeeID"));
                DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                Date date = formatter.parse(rs.getString("PayrollDate"));
                GregorianCalendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                XMLGregorianCalendar xmlCalendar = DatatypeFactory.newInstance().
                        newXMLGregorianCalendar(calendar);
                payroll.setPayrollDate(FormatUtil.dateToCalendar(rs.getDate("PayrollDate")));
                payroll.setAmount(rs.getInt("Amount"));
                payroll.setPayrollType(rs.getString("Type"));
                list.add(payroll);
            }

        } catch (NamingException ex) {
            Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return list;
    }

    public List<Payroll> getByDate(String fromDate, String toDate) throws SQLException {
        Connection connection = null;
        List<Payroll> list = new ArrayList<Payroll>();
        try {
            connection = connect();
            String sql = "SELECT * FROM Payroll WHERE ";
            if (!fromDate.isEmpty()) {
                sql += "PayrollDate >= '" + fromDate + "'";
                if (!toDate.isEmpty()) {
                    sql += " AND ";
                }
            }
            if (!toDate.isEmpty()) {
                sql += "PayrollDate <= '" + toDate + "'";
            }

            PreparedStatement stm = connection.prepareStatement(sql);
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                Payroll payroll = new Payroll();
                payroll.setPayrollID(BigInteger.valueOf(rs.getInt("PayrollID")));
                payroll.setEmployeeName(rs.getString("EmployeeID"));
                DateFormat formatter2 = new SimpleDateFormat("yyyy-MM-dd");
                Date date = formatter2.parse(rs.getString("PayrollDate"));
                GregorianCalendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                XMLGregorianCalendar xmlCalendar = DatatypeFactory.newInstance().
                        newXMLGregorianCalendar(calendar);
                payroll.setPayrollDate(xmlCalendar);
                payroll.setAmount(rs.getInt("Amount"));
                payroll.setPayrollType(rs.getString("Type"));
                list.add(payroll);
            }

        } catch (NamingException ex) {
            Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return list;
    }

    public List<Payroll> getByMonthYear(int fromMonth, int fromYear,
            int toMonth, int toYear, String empID, String role) throws SQLException {
        List<Payroll> list = new ArrayList<Payroll>();
        if (fromMonth >= 1 && fromMonth <= 12 && fromYear >= 1900) {
            Connection connection = null;
            try {
                connection = connect();
//                String sql = "SELECT * FROM Payroll p, Employee e  "
//                        + "WHERE MONTH(p.PayrollDate) >= " + fromMonth
//                        + " AND MONTH(p.PayrollDate) <= " + toMonth
//                        + " AND YEAR(p.PayrollDate) >= " + fromYear
//                        + " AND YEAR(p.PayrollDate) <= " + toYear;
//                sql += " AND p.EmployeeID = e.EmployeeID AND p.EmployeeID = " + empID;
                String sql = "SELECT * FROM Payroll p, Employee e  "
                        + "WHERE p.PayrollDate >= ?"
                        + " AND p.PayrollDate <= ?";
                sql += " AND p.EmployeeID = e.EmployeeID AND p.EmployeeID = " + empID;

                PreparedStatement stm = connection.prepareStatement(sql);
                stm.setDate(1, FormatUtil.dateToDate(FormatUtil.stringToDate(fromYear + "", fromMonth + "", "1")));
                stm.setDate(2, FormatUtil.dateToDate(FormatUtil.stringToDate(toYear + "", toMonth + 1 + "", "0")));
                ResultSet rs = stm.executeQuery();

                while (rs.next()) {
                    Payroll payroll = new Payroll();
                    payroll.setPayrollID(BigInteger.valueOf(rs.getInt("PayrollID")));
                    payroll.setEmployeeName(rs.getString("EmployeeID"));
                    DateFormat formatter2 = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = formatter2.parse(rs.getString("PayrollDate"));
                    GregorianCalendar calendar = new GregorianCalendar();
                    calendar.setTime(date);
                    XMLGregorianCalendar xmlCalendar = DatatypeFactory.newInstance().
                            newXMLGregorianCalendar(calendar);
                    payroll.setPayrollDate(xmlCalendar);
                    payroll.setAmount(rs.getInt("Amount"));
                    payroll.setPayrollType(rs.getString("Type"));
                    list.add(payroll);
                }
            } catch (NamingException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (DatatypeConfigurationException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ParseException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                if (connection != null) {
                    connection.close();
                }
            }
        }
        return list;
    }

    public List<Payroll> getByMonthYearForAdmin(int fromMonth, int fromYear,
            int toMonth, int toYear, String empID, String role, String empStatus, int amount) throws SQLException {
        List<Payroll> list = new ArrayList<Payroll>();
        if (fromMonth >= 1 && fromMonth <= 12 && fromYear >= 1900) {
            Connection connection = null;
            try {
                connection = connect();
                String sql = "SELECT * FROM Payroll p, Employee e  "
                        + "WHERE p.PayrollDate >= ?"
                        + " AND p.PayrollDate <= ?";
                if (empStatus.equals("All")) {
                } else if (empStatus.equals("Onlyme")) {
                    sql += " AND p.EmployeeID = e.EmployeeID AND p.EmployeeID = " + empID;
                } else {
                    sql += " AND p.EmployeeID = e.EmployeeID AND e.Status = '" + empStatus + "'";
                }
                sql += " AND p.Amount >= " + amount;

                PreparedStatement stm = connection.prepareStatement(sql);
                stm.setDate(1, FormatUtil.dateToDate(FormatUtil.stringToDate(fromYear + "", fromMonth + "", "1")));
                stm.setDate(2, FormatUtil.dateToDate(FormatUtil.stringToDate(toYear + "", toMonth + 1 + "", "0")));
                ResultSet rs = stm.executeQuery();

                while (rs.next()) {
                    Payroll payroll = new Payroll();
                    payroll.setPayrollID(BigInteger.valueOf(rs.getInt("PayrollID")));

                    payroll.setEmployeeName(rs.getString("EmployeeName"));

                    DateFormat formatter2 = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = formatter2.parse(rs.getString("PayrollDate"));
                    GregorianCalendar calendar = new GregorianCalendar();
                    calendar.setTime(date);
                    XMLGregorianCalendar xmlCalendar = DatatypeFactory.newInstance().
                            newXMLGregorianCalendar(calendar);
                    payroll.setPayrollDate(xmlCalendar);
                    payroll.setAmount(rs.getInt("Amount"));
                    payroll.setPayrollType(rs.getString("Type"));
                    list.add(payroll);
                }
            } catch (NamingException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (DatatypeConfigurationException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ParseException ex) {
                Logger.getLogger(PayrollBean.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                if (connection != null) {
                    connection.close();
                }
            }
        }
        return list;
    }
}
